package Or

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strings"
)

func (or TableInfoMeta) ProcedureExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[oracleProcedureExist]"
	for _, v := range []string{
		//"USER_OBJECTS",
		"ALL_OBJECTS",
		"DBA_OBJECTS",
	} {
		result.BaseResult.Sql = fmt.Sprintf("select count(1) as \"sum\" from %v where OBJECT_TYPE = 'PROCEDURE' AND STATUS != 'INVALID' and OWNER = '%v' and OBJECT_NAME='%v'", v, s.TableInfo.Schema, s.TableInfo.Table)
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil || len(f1.(map[string]any)) == 0 {
			continue
		}
		if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
			result.Result = true
		}
		break
	}
	return
}

func (or TableInfoMeta) ProcedureName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var f1 any
	var event = "[oracleProcedureName]"
	for _, v := range []string{
		//"USER_OBJECTS",
		"ALL_OBJECTS",
		"DBA_OBJECTS",
	} {
		result.BaseResult.Sql = fmt.Sprintf("select OBJECT_NAME from %v where  OBJECT_TYPE = 'PROCEDURE' AND STATUS != 'INVALID' AND OWNER ='%v' %v",
			v, s.TableInfo.Schema, func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" and OBJECT_NAME like '%%%v%%'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" and OBJECT_NAME = '%v'", s.TableInfo.Table)
				}
			}())
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.StringGarth,
		}); err != nil || f1 == nil || len(f1.([]string)) == 0 {
			continue
		}
		result.Result = f1.([]string)
		break
	}
	return
}

func productPrintInfo(x global.ProcedureResultDefinition) (res string) {
	/*
		CREATE OR REPLACE FUNCTION "BIN_TO_NUM4" (p_binstr VARCHAR2) RETURN NUMBER IS
		    l_len   NUMBER := LENGTH(p_binstr);
		    r_num   NUMBER := 0;
		BEGIN
		    FOR i IN 1 .. l_len LOOP
		        IF SUBSTR(p_binstr, i, 1) = '1' THEN
		            r_num := r_num + POWER(2, l_len - i);
		        END IF;
		    END LOOP;

		    RETURN r_num;  -- 不需要 LPAD
		END "BIN_TO_NUM4";
		/
	*/
	res = fmt.Sprintf("CREATE OR REPLACE %v"+
		"/",
		x.Body,
	)
	return
}
func productDefinitionConvert(x global.ProcedureResultDefinition) (convert global.ProcedureDefinitionConvertS) {
	return global.ProcedureDefinitionConvertS{
		Schema:      x.Schema,
		Name:        x.Name,
		Permissions: x.Permissions,
		Body:        x.Body,
		Comment:     x.Comment,
		PInput:      x.PInput,
		POutput:     x.POutput,
		Type:        x.Type,
		PrintInfo:   x.PrintInfo,
	}
}
func getProductParameter(s global.SchemaObjectInput) (input, out []global.FuncInputOutParameter, err error) {
	var f1 any
	var event = "[oracleGetProductParameter]"
	for _, v := range []string{
		//"USER_ARGUMENTS",
		"all_ARGUMENTS",
		"dba_ARGUMENTS",
	} {
		sqlStr := fmt.Sprintf("SELECT\n"+
			"\tARGUMENT_NAME AS \"pName\",\n"+
			"\tDATA_TYPE AS \"pType\",\n"+
			"\tIN_OUT AS  \"pDirection\"\n"+
			"FROM\n"+
			"\t%v \n"+
			"WHERE\n"+
			"\tOWNER = '%v' \n"+
			"\tAND OBJECT_NAME = '%v' "+
			"ORDER BY "+
			"POSITION", v, s.Schema, s.Table)
		if _, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: sqlStr,
			Expect:       ETL.SMap,
		}); err != nil || f1 == nil || len(f1.([]map[string]any)) == 0 {
			continue
		}
		for _, q := range f1.([]map[string]any) {
			var p global.FuncInputOutParameter
			if err = global.MapSubToStructSubJsonName(mapNullActive(q), &p); err != nil {
				return
			}
			switch strings.ToLower(p.PDirection) {
			case "in":
				input = append(input, p)
			case "out":
				out = append(out, p)
			default:
				out = append(out, p)
			}
		}
		break
	}

	return
}
func getProductBody(s global.SchemaObjectInput) (res string, err error) {
	var (
		f1    any
		r     []string
		event = "[oracleGetProductBody]"
	)
	for _, v := range []string{
		//"USER_source",
		"all_source",
		"dba_source",
	} {
		sqlStr := fmt.Sprintf("SELECT \n"+
			"\t\"TEXT\" AS \"text\" \n"+
			"FROM \n"+
			"%v where 1=1 \n"+
			"\tAND TYPE = 'PROCEDURE' \n"+
			"\tAND OWNER='%v' \n"+
			"\tAND NAME='%v' ORDER BY LINE", v, s.Schema, s.Table)
		if _, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: sqlStr,
			Expect:       ETL.SMap,
		}); err != nil || f1 == nil || len(f1.([]map[string]any)) == 0 {
			continue
		}
		for _, p := range f1.([]map[string]any) {
			r = append(r, fmt.Sprintf("%v", p["text"]))
		}
		res = strings.Join(r, "")
		break
	}
	return
}

func (or SchemaObjectMeta) Procedure(s global.SchemaObjectInput) (res global.SchemaObjectReturnResult, err error) {
	var (
		x = global.ProcedureResultDefinition{
			Schema: s.Schema,
			Name:   s.Table,
		}
	)
	if x.PInput, x.POutput, err = getProductParameter(s); err != nil {
		return
	}
	if x.Body, err = getProductBody(s); err != nil {
		return
	}
	x.Type = "product"
	x.PrintInfo = productPrintInfo(x)
	res.ProcedureResult.Definition = x
	res.ProcedureResult.Convert = productDefinitionConvert(x)
	return
}
